{
 "metadata": {
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.0"
  },
  "orig_nbformat": 2,
  "kernelspec": {
   "name": "pythonjvsc74a57bd01ee744fdee3946c2af2bdc58f3796819357ed068616caa870dc20aacb53d0af6",
   "display_name": "Python 3.9.0  ('venv': venv)"
  },
  "metadata": {
   "interpreter": {
    "hash": "1ee744fdee3946c2af2bdc58f3796819357ed068616caa870dc20aacb53d0af6"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2,
 "cells": [
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from peewee import *\n",
    "\n",
    "# db = SqliteDatabase(':memory:')  <-- 在内存里面搞一个数据库\n",
    "db = SqliteDatabase('my_database.db') # <--在当前目录下生成一个my_database.db文件\n",
    "\n",
    "class BaseModel(Model): # 标准写法\n",
    "    class Meta:         # 下面的Model全部继承BaseModel\n",
    "        database = db   # 连接数据库，Meta子类还可以设置其他的东西，这里别写\n",
    "\n",
    "class User(BaseModel):\n",
    "    name = CharField()\n",
    "    message = TextField()\n",
    "\n",
    "db.connect() # 生成/连接数据库\n",
    "db.close()   # 关闭 数据库连接"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "db.close()\n",
    "db.connect()\n",
    "db.create_tables([User])\n",
    "\n",
    "print(User.__dict__.keys())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = User.create(name = 'watalo', message= '11111')\n",
    "print(user)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.insert(name = 'donggua', message = 'wangwangwang').execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = User(name = 'jianhetao', message = 'houhouhou')\n",
    "user.save()\n",
    "query = User.select().where(User.name == 'jianhetao')\n",
    "for q in query:\n",
    "    print(q.message)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "user = User()\n",
    "user.name = 'jiajia' # 提示 NOT NULL constraint failed: user.message\n",
    "user.message = '555555555555555' # 如果不写这条\n",
    "user.save() "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_source = [\n",
    "    {'name':'aaa', 'message':'jjj'},\n",
    "    {'name':'bbb', 'message':'jjj'},\n",
    "    {'name':'ccc', 'message':'jjj'},\n",
    "    {'name':'ddd', 'message':'jjj'},\n",
    "    {'name':'eee', 'message':'jjj'},\n",
    "    {'name':'fff', 'message':'jjj'},\n",
    "    {'name':'ggg', 'message':'jjj'},\n",
    "    ]\n",
    "with db.atomic():\n",
    "    User.insert_many(data_source).execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "data_source = [\n",
    "    ('hhh', 'jjj'),\n",
    "    ('hhh', 'jjj'),\n",
    "    ('hhh', 'jjj'),\n",
    "    ]\n",
    "with db.atomic():\n",
    "    User.insert_many(data_source, fields=[User.name, User.message]).execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "query = User.select()\n",
    "for q in query:\n",
    "    print(q.name, q.message)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "data_source = []\n",
    "for i in range(40000):\n",
    "    data_cell = {'name':'aaa'+str(i), 'message':'message'+str(i*2)}\n",
    "    data_source.append(data_cell)\n",
    "\n",
    "print(datetime.now())\n",
    "from peewee import chunked\n",
    "with db.atomic():\n",
    "    for batch in chunked(data_source, 100):\n",
    "        User.insert_many(batch).execute()\n",
    "print(datetime.now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "data_source = []\n",
    "for i in range(40000):\n",
    "    data_cell = {'name':'aaa'+str(i), 'message':'message'+str(i*2)}\n",
    "    data_source.append(data_cell)\n",
    "\n",
    "print(datetime.now())\n",
    "\n",
    "with db.atomic():\n",
    "    for idx in range(0, len(data_source), 100):\n",
    "        User.insert_many(data_source[idx:idx+100]).execute()\n",
    "print(datetime.now())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "timedelte = 37.499742 - 36.792754\n",
    "timedelte2 = 14.249236 - 13.540237\n",
    "print(timedelte, timedelte2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 先获得一条记录\n",
    "watalo = User.get(User.name == 'watalo')\n",
    "watalo.delete_instance()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get(User.name == 'hhh') \n",
    "# instance matching query does not exist"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hhh_to_be_delete = User.delete().where(User.name == 'hhh') \n",
    "hhh_to_be_delete.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "aaa = User.select().where(User.name == 'aaa').get()\n",
    "aaa.message = '我改了'\n",
    "aaa.save()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "query = User.update(message = '').where(User.name.startswith('aaa'))\n",
    "query.execute()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "class Member(Model):\n",
    "    username = TextField(unique=True)\n",
    "    last_login = DateTimeField(null=True)\n",
    "\n",
    "member_id = Member.replace(username='the-user', last_login = datetime.now())\n",
    "print(member_id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get(User.id == 6)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get_by_id(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User[8]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get(User.id == 9).name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get(User.name == 'aaa')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.select().where(User.name == 'bbb').get()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get_or_none(name == 'hhh')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "User.get_or_create(name = 'wocalie',defaults = {'message':'successed'})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": []
   },
   "outputs": [],
   "source": [
    "query = User.select()\n",
    "print(query)\n",
    "# for q in query:\n",
    "#     print(q.name, q.message)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "query = User.select()\n",
    "for row in query:\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "query = User.select().dicts()\n",
    "\n",
    "for row in query.iterator():\n",
    "    print(row)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "tags": [
     "outputPrepend"
    ]
   },
   "outputs": [],
   "source": [
    "# 当联表查询时，peewee会重构一个model，这样很慢，用objects()可以加快速度，直接调用数据。\n",
    "query = User.select()\n",
    "\n",
    "for q in query.objects():\n",
    "    print(q.name)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "class Corp(BaseModel):\n",
    "    name = CharField() \n",
    "\n",
    "class Sheet(BaseModel):\n",
    "    corp = ForeignKeyField(Corp, backref='sheets')\n",
    "    durition = DateField()\n",
    "\n",
    "class Balance(BaseModel):\n",
    "    sheet = ForeignKeyField(Sheet, backref='bs')\n",
    "    asset = DecimalField()\n",
    "    debt = DecimalField()\n",
    "    equity = DecimalField()\n",
    "\n",
    "db.create_tables([Corp, Sheet, Balance])\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import date\n",
    "\n",
    "for i in range(100):\n",
    "    corp1 = Corp.create(name = 'MTT')\n",
    "    sheet1 = Sheet.create(corp = corp1, durition = date(2020,12,31))\n",
    "    Balance.create(duty = sheet1, asset = 100000, debt = 50000, equity = 100000-50000)\n",
    "    i += 1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "\n",
    "query = Balance.select().join(Corp, Sheet)\n",
    "print(datetime.now())\n",
    "for q in query:\n",
    "    print(q.sheets.name.name)\n",
    "\n",
    "\n",
    "print(datetime.now())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "from peewee import *\n",
    "       \n",
    "# db = SqliteDatabase(':memory:')  <-- 在内存里面搞一个数据库\n",
    "db = SqliteDatabase('my_another_database.db') # <--在当前目录下生成一个my_database.db文件\n",
    "\n",
    "class BaseModel(Model): # 标准写法\n",
    "    class Meta:         # 下面的Model全部继承BaseModel\n",
    "        database = db   # 连接数据库，Meta子类还可以设置其他的东西，这里别写\n",
    "\n",
    "class Member(BaseModel):\n",
    "    name = CharField()\n",
    "    message = TextField()\n",
    "\n",
    "class Tweet(BaseModel):\n",
    "    member = ForeignKeyField(Member, backref = 'works')\n",
    "    content = TextField()\n",
    "    is_published = BooleanField()\n",
    "    created_date = DateTimeField()\n",
    "\n",
    "\n",
    "db.create_tables([Member, Tweet])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "from datetime import datetime\n",
    "\n",
    "for i in range(50):\n",
    "    name = 'user_{}'.format(1+i)\n",
    "    memb = Member.create(name = name, message = 'test_message_i'.format(1+i))\n",
    "    Tweet.create(member = memb, content = 'test', is_published = True, created_date = datetime.now())\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "user_50 test_message_i test 2021-04-18 11:27:45.557260\nuser_49 test_message_i test 2021-04-18 11:27:45.552136\nuser_48 test_message_i test 2021-04-18 11:27:45.548074\nuser_47 test_message_i test 2021-04-18 11:27:45.544563\nuser_46 test_message_i test 2021-04-18 11:27:45.541102\nuser_45 test_message_i test 2021-04-18 11:27:45.537349\nuser_44 test_message_i test 2021-04-18 11:27:45.534100\nuser_43 test_message_i test 2021-04-18 11:27:45.531399\nuser_42 test_message_i test 2021-04-18 11:27:45.526569\nuser_41 test_message_i test 2021-04-18 11:27:45.523041\nuser_40 test_message_i test 2021-04-18 11:27:45.518529\nuser_39 test_message_i test 2021-04-18 11:27:45.515847\nuser_38 test_message_i test 2021-04-18 11:27:45.512166\nuser_37 test_message_i test 2021-04-18 11:27:45.509241\nuser_36 test_message_i test 2021-04-18 11:27:45.506427\nuser_35 test_message_i test 2021-04-18 11:27:45.502379\nuser_34 test_message_i test 2021-04-18 11:27:45.498869\nuser_33 test_message_i test 2021-04-18 11:27:45.495509\nuser_32 test_message_i test 2021-04-18 11:27:45.492111\nuser_31 test_message_i test 2021-04-18 11:27:45.487411\nuser_30 test_message_i test 2021-04-18 11:27:45.483723\nuser_29 test_message_i test 2021-04-18 11:27:45.479743\nuser_28 test_message_i test 2021-04-18 11:27:45.476629\nuser_27 test_message_i test 2021-04-18 11:27:45.474337\nuser_26 test_message_i test 2021-04-18 11:27:45.467403\nuser_25 test_message_i test 2021-04-18 11:27:45.464154\nuser_24 test_message_i test 2021-04-18 11:27:45.460970\nuser_23 test_message_i test 2021-04-18 11:27:45.457675\nuser_22 test_message_i test 2021-04-18 11:27:45.452081\nuser_21 test_message_i test 2021-04-18 11:27:45.448971\nuser_20 test_message_i test 2021-04-18 11:27:45.444998\nuser_19 test_message_i test 2021-04-18 11:27:45.441503\nuser_18 test_message_i test 2021-04-18 11:27:45.436931\nuser_17 test_message_i test 2021-04-18 11:27:45.433603\nuser_16 test_message_i test 2021-04-18 11:27:45.429932\nuser_15 test_message_i test 2021-04-18 11:27:45.426291\nuser_14 test_message_i test 2021-04-18 11:27:45.423277\nuser_13 test_message_i test 2021-04-18 11:27:45.419112\nuser_12 test_message_i test 2021-04-18 11:27:45.416365\nuser_11 test_message_i test 2021-04-18 11:27:45.413348\nuser_10 test_message_i test 2021-04-18 11:27:45.409318\nuser_9 test_message_i test 2021-04-18 11:27:45.404492\nuser_8 test_message_i test 2021-04-18 11:27:45.401001\nuser_7 test_message_i test 2021-04-18 11:27:45.398156\nuser_6 test_message_i test 2021-04-18 11:27:45.390114\nuser_5 test_message_i test 2021-04-18 11:27:45.386465\nuser_4 test_message_i test 2021-04-18 11:27:45.382934\nuser_3 test_message_i test 2021-04-18 11:27:45.377617\nuser_2 test_message_i test 2021-04-18 11:27:45.369261\nuser_1 test_message_i test 2021-04-18 11:27:45.362042\n"
     ]
    }
   ],
   "source": [
    "query = Tweet.select().join(Member).where(Tweet.is_published).order_by(Tweet.created_date.desc())\n",
    "for q in query:\n",
    "    print(q.member.name, q.member.message, q.content, q.created_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "41\n42\n43\n44\n45\n46\n47\n48\n49\n50\n"
     ]
    }
   ],
   "source": [
    "for tweet in Tweet.select().paginate(5, 10):\n",
    "    print(tweet.id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "26\n23\n24\n11\n10\n"
     ]
    }
   ],
   "source": [
    "for tweet in Tweet.select().order_by(fn.Random()).limit(5):\n",
    "    print(tweet.id)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "10"
      ]
     },
     "metadata": {},
     "execution_count": 9
    }
   ],
   "source": [
    "Tweet.select().where(Tweet.id > 40).count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "output_type": "execute_result",
     "data": {
      "text/plain": [
       "50"
      ]
     },
     "metadata": {},
     "execution_count": 10
    }
   ],
   "source": [
    "Tweet.select().count()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "output_type": "stream",
     "name": "stdout",
     "text": [
      "1 0\n2 1\n3 1\n4 1\n5 1\n6 1\n7 1\n8 1\n9 1\n10 1\n11 1\n12 1\n13 1\n14 1\n15 1\n16 1\n17 1\n18 1\n19 1\n20 1\n21 1\n22 1\n23 1\n24 1\n25 1\n26 1\n27 1\n28 1\n29 1\n30 1\n31 1\n32 1\n33 1\n34 1\n35 1\n36 1\n37 1\n38 1\n39 1\n40 1\n41 1\n42 1\n43 1\n44 1\n45 1\n46 1\n47 1\n48 1\n49 1\n50 1\n51 1\n"
     ]
    }
   ],
   "source": [
    "query = (Member\n",
    "         .select(Member, fn.Count(Tweet.id).alias('count'))\n",
    "         .join(Tweet, JOIN.LEFT_OUTER)\n",
    "         .group_by(Member))\n",
    "for q in query:\n",
    "    print(q.id, q.count)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "### 该部分可以完结了\n",
    "import peewee "
   ]
  }
 ]
}